﻿CREATE FUNCTION [dbo].[Collection_Borrowers_](
	@UsixID varchar(8),
	@CompanyID int
)RETURNS TABLE 
RETURN 
SELECT     CF_E.ApplID, L_B.Pos, CF_E.Name AS Name, ISNULL(B0.SSN, CF_E.SSN) AS SSN, 
                      ISNULL(NULLIF (A.Street, ''), CF_E.Address) AS Street, ISNULL(NULLIF (A.CityName, ''), CF_E.City) AS CityName, ISNULL(NULLIF (A.Zip, ''), 
                      LEFT(CF_E.[Zip Code],5)) AS Zip, ISNULL(NULLIF (A.StateCode, ''), CF_E.St) AS StateCode, REPLACE(ISNULL(NULLIF (B0.PhoneHome, ''), 
                      CF_E.[Home Phone #]), '-', '') AS PhoneHome, REPLACE(ISNULL(NULLIF (B0.PhoneCell, ''), CF_E.[Primary Cell#]), '-', '') AS PhoneCell, 
                      REPLACE(ISNULL(NULLIF (E.Phone, ''), CF_E.[Work Phone #]), '-', '') AS PhoneWork, ISNULL(L_B.BorrowerID,0)BorrowerID
FROM         t_Borrower AS B0 INNER JOIN
                      t_Loan_Borrower AS L_B ON B0.ID = L_B.BorrowerID AND L_B.Pos = 0 INNER JOIN
                      t_Address AS A ON B0.ID = A.BorrowerID AND A.Pos = 0 INNER JOIN
                      t_Employment AS E ON B0.ID = E.BorrowerID AND E.JobNumber = 0 AND E.Pos = 0 RIGHT OUTER JOIN
                      usix.t_CustomerFile_Export AS CF_E ON L_B.LoanID = CF_E.ApplID
WHERE     (CF_E.UsixID = @UsixID) AND (CF_E.CompanyID_Raw = @CompanyID)
UNION ALL
SELECT     CF_E.ApplID, ISNULL(L_B.Pos, 1) AS Expr1, CF_E.[Joint Name] AS Name, ISNULL(B0.SSN, 
                      CF_E.[Joint SSN]) AS SSN, ISNULL(NULLIF (A.Street, ''), CF_E.[Joint Address]) AS Street, ISNULL(NULLIF (A.CityName, ''), CF_E.[Joint City]) 
                      AS CityName, ISNULL(NULLIF (A.Zip0, ''), ISNULL(LEFT(CF_E.[Joint Zipcode],5),'')) AS Zip, ISNULL(NULLIF (A.StateCode, ''), CF_E.[Joint State]) AS StateCode, 
                      REPLACE(ISNULL(NULLIF (B0.PhoneHome, ''), CF_E.[Joint H Phone#]), '-', '') AS PhoneHome, REPLACE(ISNULL(NULLIF (B0.PhoneCell, ''), 
                      CF_E.[Joint Cell#]), '-', '') AS PhoneCell, REPLACE(ISNULL(NULLIF (E.Phone, ''), CF_E.[Joint W Phone#]), '-', '') AS PhoneWork, ISNULL(L_B.BorrowerID,0)BorrowerID
FROM         t_Borrower AS B0 INNER JOIN
                      t_Loan_Borrower AS L_B ON B0.ID = L_B.BorrowerID AND L_B.Pos = 1 INNER JOIN
                      t_Address AS A ON B0.ID = A.BorrowerID AND A.Pos = 0 INNER JOIN
                      t_Employment AS E ON B0.ID = E.BorrowerID AND E.JobNumber = 0 AND E.Pos = 0 RIGHT OUTER JOIN
                      usix.t_CustomerFile_Export AS CF_E ON L_B.LoanID = CF_E.ApplID
WHERE     (CF_E.UsixID = @UsixID) AND (CF_E.CompanyID_Raw = @CompanyID) AND (ISNULL(B0.LastName + ', ' + B0.FirstName, CF_E.[Joint Name]) > '')
/*
SELECT     CF_E.ApplID,ISNULL(L_B.Pos,1), ISNULL(B0.LastName + ', ' + B0.FirstName, CF_E.[Joint Name]) AS Name, ISNULL(B0.SSN, CF_E.[Joint SSN]) AS SSN
FROM         t_Borrower AS B0 INNER JOIN
                      t_Loan_Borrower AS L_B ON B0.ID = L_B.BorrowerID RIGHT OUTER JOIN
                      usix.t_CustomerFile_Export AS CF_E ON L_B.LoanID = CF_E.ApplID AND (L_B.Pos = 1)
WHERE     (CF_E.UsixID = @UsixID) AND (CF_E.CompanyID = @CompanyID) AND ISNULL(B0.LastName + ', ' + B0.FirstName, CF_E.[Joint Name])>''

*/


--SELECT * FROM Collection_Borrowers('DA105794',2)


